/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ContentManagers.RoleManagers;

import ContentManagers.Models.AccountingManager;
import ContentManagers.Security.Authenticator;
import ContentManagers.Models.ProductManager;
import ContentManagers.Models.Product;
import ContentManagers.Security.Validator;
import DBConnection.ConnectionFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Ervin James
 */
public class PMManager {
    
    private Calendar getCurrentDate()
    {
        Calendar date = Calendar.getInstance();
        
        return date;
    }
    
    private Calendar getSpecifiedDate(String date)
    {
        SimpleDateFormat dateformatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        Calendar cal = Calendar.getInstance();
        try {
            Date sDate = dateformatter.parse(date);   
            cal.setTime(sDate);
        } catch (ParseException ex) {
            Logger.getLogger(AMManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return cal;
    }
    
    private long getHours(Calendar date1, Calendar date2)
    {
        long diff = date1.getTimeInMillis() - date2.getTimeInMillis();
        
        long hours = diff / (1000 * 60 * 60);
        
        return hours;
    }
    
    public boolean isPasswordExpired(ProductManager pm)
    {
         boolean result = false;

        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM pm_accounts WHERE username = ?");

            pstmt.setString(i++, pm.getUsername());
                     
            ResultSet rs = pstmt.executeQuery();

            while(rs.next())
            {  
                if(getHours(getCurrentDate(), getSpecifiedDate(rs.getString("password_timestamp"))) > 24)          
                    result = true;
                //System.out.println(getHours(getCurrentDate(), getSpecifiedDate(rs.getString("password_timestamp"))));
            }
            
            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return result;  
    }
    
    public boolean authenticatePM (ProductManager pm)
    {
        
        boolean result = false;

        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM pm_accounts WHERE username = ?");

            pstmt.setString(i++, pm.getUsername());
                     
            ResultSet rs = pstmt.executeQuery();

            while(rs.next())
            {
              if(Authenticator.CompareHash(pm.getPassword(), rs.getString("password"), rs.getString("password_salt")))
                result = true;
               
            }
            

            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return result;
        
    }
    
    public int getProductTypeID(String username)
    {
        int pt=0;
 
         try {
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();
          
            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product_type where product_type_id = (SELECT manager_type_id FROM product_manager where username = ?)");
            pstmt.setString(i++, username);
            
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {         
                pt = rs.getInt("product_type_id");
            }
            conn.close();
            
       }  catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        
        return pt;
    }
    
    public boolean checkProduct (Product newProduct)
    {
        boolean check = false;
        
        
        try {
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();
          
            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product WHERE product_name = ?");
            pstmt.setString(i++, newProduct.getName());
            
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {         
                check = true;
            }
            conn.close();
            
        }  catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return check;
    }
    
      public boolean checkProduct2 (Product newProduct)
    {
        boolean check = false;
        
        
        try {
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();
          
            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product WHERE product_name = ? AND product_id != ?");
            pstmt.setString(i++, newProduct.getName());
            pstmt.setInt(i++, newProduct.getPid());
            
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {         
                check = true;
            }
            conn.close();
            
        }  catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return check;
    }
    
    public boolean addProduct(Product newProduct)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;

            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO product (product_name, description ,price, product_type_id) VALUES(?,?,?,?)");

            //include parameters
            
            pstmt.setString(i++, newProduct.getName());
            pstmt.setString(i++, newProduct.getDescription());
            pstmt.setDouble(i++, newProduct.getPrice());
            pstmt.setInt(i++, newProduct.getPtid());
            

            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
     
       public boolean editProduct(Product newProduct)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("UPDATE product SET product_name=?, description=?, price=? WHERE product_id=?");

             //include parameters
            pstmt.setString(i++, newProduct.getName());
            pstmt.setString(i++, newProduct.getDescription());
            pstmt.setDouble(i++, newProduct.getPrice());
            pstmt.setInt(i++, newProduct.getPid());
             
            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
       
    public boolean deleteProduct(Product newProduct)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("UPDATE product set isAvailable = 0 WHERE product_id= ?");
            
            //include parameters
            pstmt.setInt(i++, newProduct.getPid());
           

            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
    public boolean restoreProduct(Product newProduct)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("UPDATE product set isAvailable = 1 WHERE product_id= ?");
            
            //include parameters
            pstmt.setInt(i++, newProduct.getPid());
           

            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
    public ResultSet searchItem(String keyword, String productType)
    {   //int i = 1;
            
        try {
            //get connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("customer");
            Connection conn = myFactory.getConnection();

            int i = 1;
            String cleanKeyword = Validator.inputSanitizer(keyword);
            
            //Construct SQL Query
            PreparedStatement pstmt;
            
            //System.out.print("---" + productType);
            
            if(productType.equals("0") == true)
                pstmt = conn.prepareStatement("SELECT * from product WHERE isAvailable = 1 AND product_name LIKE '%" + cleanKeyword + "%'" + " OR description LIKE '%" + cleanKeyword + "%'");
            else
            {   pstmt = conn.prepareStatement("SELECT * from product WHERE isAvailable = 1 AND product_type_id = ? AND product_id IN (SELECT product_id FROM product WHERE product_name LIKE '%" + cleanKeyword + "%'" + " OR description LIKE '%" + cleanKeyword + "%')");
                pstmt.setString(i++, Validator.inputSanitizer(productType));
            }
            //System.out.print("---" + productType);}
            
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();

            return result;

            }   
         catch (SQLException ex) {ex.printStackTrace();return null;}
    }
    
    public ResultSet getProduct(String productID)
    {
        try {
            //get connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;
            //Construct SQL Query
            PreparedStatement pstmt;
            
            pstmt = conn.prepareStatement("SELECT * from product WHERE product_id = ?");
            pstmt.setString(i++, Validator.inputSanitizer(productID));
            
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();

            return result;

            }
         catch (SQLException ex) {ex.printStackTrace();return null;}
        
    }
    
    public ResultSet getProductTypes()
    {
        try {
            //get connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;
            
            //Construct SQL Query
            PreparedStatement pstmt;
            
            pstmt = conn.prepareStatement("SELECT product_type_name, product_type_id from product_type");
            
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();

            return result;

            }
         catch (SQLException ex) {ex.printStackTrace();return null;}
    }
    
    public boolean userHasBought(String username, String product_id)
    {
        
        boolean result = false;

        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory("customer");
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM purchased_for_review WHERE username = ? AND product_id = ?");

            pstmt.setString(i++, username);
            pstmt.setString(i++, product_id);
                     
            ResultSet rs = pstmt.executeQuery();

            while(rs.next())
            {
              result = true;
            }
            

            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return result;
     
    }
    
    public ResultSet getProductList(int product_id)
    {
         try {
            //get connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;
            
            //Construct SQL Query
            PreparedStatement pstmt;
            
            pstmt = conn.prepareStatement("SELECT * from product where product_type_id = ? ORDER BY isAvailable DESC");
            
            pstmt.setInt(i++, product_id);
            
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();

            return result;

            }
         catch (SQLException ex) {ex.printStackTrace();return null;}
    }
}
